IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_Report_IssueByResult')
	BEGIN
		DROP  Procedure  up_Report_IssueByResult
	END

GO

CREATE Procedure up_Report_IssueByResult
	@ProjectId int = null
AS

--HANDLE OLD VERSION
IF (@ProjectId IS NULL)
BEGIN
	SET @ProjectId = 6
END

select
	RT.Vendor_InspVisit_XmissionStructures_ResultTypeId AS resultTypeId,
	RT.Name as resultTypeName,
	count(*) as amountPoles,
	CASE  
		WHEN RT.Vendor_InspVisit_XmissionStructures_ResultTypeId =  1   THEN 'Passed Inspection'
		WHEN RT.Vendor_InspVisit_XmissionStructures_ResultTypeId =  2   THEN 'Passed with Comments or Some Concern'
		WHEN RT.Vendor_InspVisit_XmissionStructures_ResultTypeId =  3   THEN 'Rejected Pole/Asset'
		WHEN RT.Vendor_InspVisit_XmissionStructures_ResultTypeId =  4   THEN 'Rejected Pole/Asset - Priority'
		WHEN RT.Vendor_InspVisit_XmissionStructures_ResultTypeId =  5   THEN 'Rejected Pole/Asset - High Priority'
		WHEN RT.Vendor_InspVisit_XmissionStructures_ResultTypeId =  6   THEN 'Rejected Pole/Asset - Immediate Action Required'
	END AS resultTypeDescriptionAmount
from tb_Vendor_InspVisit_XmissionStructures_ResultTypes RT
LEFT JOIN tb_Vendor_InspVisit_XmissionStructures_Results RP
	ON RT.Vendor_InspVisit_XmissionStructures_ResultTypeId = RP.Vendor_InspVisit_XmissionStructures_ResultTypeFk
LEFT join  tb_Vendor_InspVisit_XmissionStructures_Poles IP 
	ON RP.Vendor_InspVisit_XMissionStructure_PoleFk = IP.Vendor_InspVisit_XMissionStructure_PoleId
LEFT JOIN tb_Utility_Asset_XMissionStructures_Poles AP
	ON IP.Utility_Asset_XMissionStructures_PoleFk = AP.Utility_Asset_XMissionStructures_PoleId
where IP.Vendor_ProjectFk  = @ProjectId
group by RT.Vendor_InspVisit_XmissionStructures_ResultTypeId,RT.Name
order by RT.Vendor_InspVisit_XmissionStructures_ResultTypeId





GO

GRANT EXEC ON up_Report_IssueByResult TO PPIReports

GO

